SQL Server 的 CAST 與 CONVERT 可以讓我們明確地轉換運算式的資料型別,這次我們就是要討論如何使用這兩個函式來格式化日期與時間資料。
CAST 與 CONVERT 都提供類似的功能讓我們明確地轉換運算式的資料型別,我們就先看看 CONVERT 的語法:
CONVERT ( 資料型別 [ (資料長度) ] , 運算式 [ , 日期格式樣式 ] )
由上面的語法可以看出來 CONVERT 擁有指定**「日期格式樣式」**的選項,就讓我們透過實際的例子來看看轉換效果:
-- 定義變數
DECLARE @myDate DATETIME
-- 指派一個日期給該變數
SET @myDate = '2008/09/09 08:25 AM'
-- 開始轉換
PRINT CONVERT(CHAR(19), @myDate)
PRINT CONVERT(CHAR(19), @myDate, 0)
PRINT CONVERT(CHAR(8), @myDate, 1)
PRINT CONVERT(CHAR(8), @myDate, 2)
PRINT CONVERT(CHAR(8), @myDate, 3)
PRINT CONVERT(CHAR(8), @myDate, 4)
PRINT CONVERT(CHAR(8), @myDate, 5)
PRINT CONVERT(CHAR(9), @myDate, 6)
PRINT CONVERT(CHAR(10), @myDate, 7)
PRINT CONVERT(CHAR(8), @myDate, 8)
PRINT CONVERT(CHAR(26), @myDate, 9)
PRINT CONVERT(CHAR(8), @myDate, 10)
PRINT CONVERT(CHAR(8), @myDate, 11)
PRINT CONVERT(CHAR(6), @myDate, 12)
PRINT CONVERT(CHAR(24), @myDate, 13)
PRINT CONVERT(CHAR(12), @myDate, 14)
PRINT CONVERT(CHAR(19), @myDate, 20)
PRINT CONVERT(CHAR(23), @myDate, 21)
PRINT CONVERT(CHAR(20), @myDate, 22)
PRINT CONVERT(CHAR(10), @myDate, 23)
PRINT CONVERT(CHAR(8), @myDate, 24)
PRINT CONVERT(CHAR(23), @myDate, 25)
PRINT CONVERT(CHAR(19), @myDate, 100)
PRINT CONVERT(CHAR(10), @myDate, 101)
PRINT CONVERT(CHAR(10), @myDate, 102)
PRINT CONVERT(CHAR(10), @myDate, 103)
PRINT CONVERT(CHAR(10), @myDate, 104)
PRINT CONVERT(CHAR(10), @myDate, 105)
PRINT CONVERT(CHAR(11), @myDate, 106)
PRINT CONVERT(CHAR(12), @myDate, 107)
PRINT CONVERT(CHAR(8), @myDate, 108)
PRINT CONVERT(CHAR(26), @myDate, 109)
PRINT CONVERT(CHAR(10), @myDate, 110)
PRINT CONVERT(CHAR(10), @myDate, 111)
PRINT CONVERT(CHAR(8), @myDate, 112)
PRINT CONVERT(CHAR(24), @myDate, 113)
PRINT CONVERT(CHAR(12), @myDate, 114)
PRINT CONVERT(CHAR(19), @myDate, 120)
PRINT CONVERT(CHAR(23), @myDate, 121)
PRINT CONVERT(CHAR(23), @myDate, 126)
PRINT CONVERT(CHAR(23), @myDate, 127)
PRINT CONVERT(CHAR(32), @myDate, 130)
PRINT CONVERT(CHAR(25), @myDate, 131)
執行結果:
接下來看 CAST 的語法:
CAST (運算式 AS 資料型別 [ (資料長度) ])
與 CONVERT 相比,CAST 馬上就遜掉了,因為 CAST 沒有辦法指定日期格式樣式,如果要指定的話,勢必要自己加工過。
例如,我們要像 YYYY-MM-DD HH:MI:SS.sss 這樣的結果:
-- 定義變數
DECLARE @myDate DATETIME
-- 指派一個日期給該變數
SET @myDate = '2008/09/09 08:25 AM'
-- 開始轉換
PRINT '使用 CONVERT 轉換 ==> ' +
CONVERT(CHAR(23), @myDate, 21)
PRINT '使用 CAST 轉換 =====> ' +
CAST(DATEPART(YY, @myDate) AS CHAR(4)) + '-'
+ RIGHT(CAST(100 + DATEPART(MM, @myDate) AS CHAR(3)), 2) + '-'
+ RIGHT(CAST(100 + DATEPART(DD, @myDate) AS CHAR(3)), 2) + ' '
+ RIGHT(CAST(100 + DATEPART(HH, @myDate) AS CHAR(3)), 2) + ':'
+ RIGHT(CAST(100 + DATEPART(MI, @myDate) AS CHAR(3)), 2) + ':'
+ RIGHT(CAST(100 + DATEPART(SS, @myDate) AS CHAR(3)), 2) + '.'
+ RIGHT(CAST(1000+ DATEPART(MS, @myDate) AS CHAR(4)), 3)
執行結果:
接下來我們直接透過實際的程式碼,討論如何把 AM 或 PM 換成中文的上午或下午:
DECLARE @myDate DATETIME
SET @myDate = '2008/09/09 08:25 AM'
SELECT CAST(DATEPART(YYYY, @myDate) AS CHAR(4)) + '/'
+ RIGHT(CAST(100 + DATEPART(MM, @myDate) AS CHAR(3)), 2) + '/'
+ RIGHT(CAST(100 + DATEPART(DD, @myDate) AS CHAR(3)), 2) + ' '
+ CASE WHEN DATEPART(HH, @myDate) < 13
THEN RIGHT(CAST(100 + DATEPART(HH, @myDate) AS CHAR(3)), 2)
ELSE CAST(DATEPART(HH, @myDate) - 12 AS CHAR(2))
END + ':'
+ RIGHT(CAST(100 + DATEPART(MI, @myDate) AS CHAR(3)), 2)
+ CASE WHEN DATEPART(HH, @myDate) < 13
THEN '上午'
ELSE '下午'
END
執行結果:
最後,就以轉換西元日期成為民國年作為最後一個轉換的範例吧:
DECLARE @myDate DATETIME
SET @myDate = '2008/09/09 08:25 AM'
SELECT CAST(YEAR(@myDate) - 1911 AS NVARCHAR(3)) + '年'
+ CAST(Month(@myDate) AS NVARCHAR(2)) + '月'
+ CAST(Day(@myDate) AS NVARCHAR(2)) + '日'
執行結果: